﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsFormsApplication1
{
     public  class MDBHelper
    {

         public DataTable BatchInfo(string BatchId, string ParmMDBConnStr)
         {
             OleDbConnection FlashConn;
             DataTable dt;
             OleDbDataAdapter da;
             FlashConn = new OleDbConnection(ParmMDBConnStr);
             FlashConn.Open();
             dt = new DataTable();
             StringBuilder sb = new StringBuilder();
             //   sb.Append("select * from BatchList batch");
             //,valset.Size
             //        ,valset.LengthUp ,valset.LengDown,valset.HighUp,valset.HighDown,
             //        valset.QXUp,valset.QXDwon
             sb.Append(string.Format(@"select BatchID ,ItemID,ProID from BatchList where  BatchId ='" + BatchId + "'"));

             da = new OleDbDataAdapter(sb.ToString(), FlashConn);
             da.Fill(dt);

             return dt;
         }
         //查询数据
         public DataTable ItemInfo( string where, string ParmMDBConnStr)
         {
             OleDbConnection FlashConn;
             DataTable dt;
             OleDbDataAdapter da;
             FlashConn = new OleDbConnection(ParmMDBConnStr);
             FlashConn.Open();
             dt = new DataTable();
             StringBuilder sb = new StringBuilder();

             sb.Append(string.Format(@"select val.Sizea as  规格 ,infotype.Name as 规格名称  ,val.Fielda as 领域, 
                    val.LengUp as 长上限,val.LengDown as 长下限,val.HighUp as 高上限,val.HighDown as 高下限,
                val.QXUp as 厚度上限,val.QXDown as 厚度下限,val.Typea
             from  ValueSet val
              left join TypeInfo  infotype on infotype.Typea=val.Typea
  where 1=1 " + where ));

             da = new OleDbDataAdapter(sb.ToString(), FlashConn);
             da.Fill(dt);

             return dt;
         }
         public DataTable getMasterRe (string ParmMDBConnStr, string ItemId, string Prodid, string BatchId, string TypeId)
         {
             OleDbConnection FlashConn;
             DataTable dt;
             OleDbDataAdapter da;
             FlashConn = new OleDbConnection(ParmMDBConnStr);
             FlashConn.Open();
             dt = new DataTable();
             StringBuilder sb = new StringBuilder();

             sb.Append(string.Format(@"
        select OrderNo as 序号,QCValue,QCItemId from QCRecords where ItemId='" + ItemId + "' and Prodid='" + Prodid + "'  and BatchId='" + BatchId + "'  and TypeId='" + TypeId + "'"
                                                                      + " and DeleteFlag=0 and OrderNo=1 order by QcItemId "
                                                                     ));

             da = new OleDbDataAdapter(sb.ToString(), FlashConn);
             da.Fill(dt);

             return dt;
         }

          //获得图例内容
         public DataTable getMaster(string ParmMDBConnStr ,string BatchId)
         {
             SqlConnection FlashConn = new SqlConnection(ParmMDBConnStr);
             FlashConn.Open();
             DataTable dt = new DataTable();
             StringBuilder sb = new StringBuilder();

//             sb.Append(string.Format(@"
////        select OrderNo,QCValue,QCItemId from QCRecords where ItemId='" + ItemId + "' and Prodid='" + Prodid + "'  and BatchId='" + BatchId + "' "
////                                                                      + " and DeleteFlag=0 and OrderNo='1' order by QcItemId "
////                                                                     ));

             sb.Append(string.Format(@"select OrderNo,QCValue,QCItemId,CreatedDatetime from MicrometerData where  BatchNo='" + BatchId + "'and DelFlag = 0 and OrderNo = '" + 1 + "' order by QcItemId "));

             SqlDataAdapter da = new SqlDataAdapter(sb.ToString(), FlashConn);
             da.Fill(dt);

             return dt;      
         }

         public Int32 getmax(string ParmMDBConnStr, string ItemId, string Prodid, string BatchId, string TypeId)
         {
             OleDbConnection FlashConn;
             DataTable dt;
             OleDbDataAdapter da;
             FlashConn = new OleDbConnection(ParmMDBConnStr);
             FlashConn.Open();
             dt = new DataTable();
             StringBuilder sb = new StringBuilder();

             sb.Append(string.Format(@"
        select   OrderNo  from QCRecords  where ItemId='" + ItemId + "' and Prodid='" + Prodid + "'  and BatchId='" + BatchId + "'  and TypeId='" + TypeId + "'"
                                                                      + " and DeleteFlag=0 order by OrderNo desc  "
                                                                     ));

             da = new OleDbDataAdapter(sb.ToString(), FlashConn);
             da.Fill(dt);
             if (dt.Rows.Count > 0)
             {
                 // dt.Rows.
                 return Convert.ToInt32(dt.Rows[0][0]);
             }
             else
             {
                 return 0;
             }
         }
         public DataTable getMasterData(string ParmMDBConnStr,string BatchId)
         {
             SqlConnection FlashConn = new SqlConnection(ParmMDBConnStr);
             FlashConn.Open();
             DataTable dt = new DataTable();
             StringBuilder sb = new StringBuilder();

             sb.Append(string.Format(@"select OrderNo,QCValue,QCItemId,CreatedDatetime from MicrometerData where BatchNo='" + BatchId + "'and DelFlag = 0 order by OrderNo,QcItemId ,CreatedDatetime"));

             SqlDataAdapter da = new SqlDataAdapter(sb.ToString(), FlashConn);
             da.Fill(dt);

             return dt;
         }
         public DataTable getorder(string ParmMDBConnStr, string BatchId)
         {
             SqlConnection FlashConn = new SqlConnection(ParmMDBConnStr);
             FlashConn.Open();
             DataTable dt = new DataTable();
             StringBuilder sb = new StringBuilder();

             sb.Append(string.Format(@"select distinct OrderNo from MicrometerData where BatchNo='" + BatchId + "'and DelFlag = 0  order by OrderNo "));

             SqlDataAdapter da = new SqlDataAdapter(sb.ToString(), FlashConn);
             da.Fill(dt);

             return dt;
         }
         public string  getnametype(string Name, string ParmMDBConnStr)
         {
             OleDbConnection FlashConn;
             DataTable dt;
             OleDbDataAdapter da;
             FlashConn = new OleDbConnection(ParmMDBConnStr);
             FlashConn.Open();
             dt = new DataTable();
             StringBuilder sb = new StringBuilder();
             //   sb.Append("select * from BatchList batch");
             //,valset.Size
             //        ,valset.LengthUp ,valset.LengDown,valset.HighUp,valset.HighDown,
             //        valset.QXUp,valset.QXDwon
            
             sb.Append(string.Format(@"select Typea from TypeInfo where  Name ='" + Name + "'"));
             //半坯检验标准
             //半坯检验标准
             da = new OleDbDataAdapter(sb.ToString(), FlashConn);
             da.Fill(dt);
             if (dt.Rows.Count > 0)
                 return dt.Rows[0][0].ToString();
             else
                 return "";//或者null
         }
    }
}
